package cn.edu.seu.cose.qol.model.dao.database.question;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.po.question.Questionnaire;
import cn.edu.seu.cose.qol.model.po.question.ShortAnswer;


public class ShortAnswerDataAccess {
	
	private static final String COLUMN_SHORT_ANSWER_ID =
			"question_short_answer_id";
	
	private static final String COLUMNE_QUESTIONNAIRE_ID =
			"fk_questionnaire_id";
	
	private static final String COLUMN_QUESTION_TITLE = "question_title";
	
	private static final String STATEMENT_RETRIEVE_SHORT_ANSWER_BY_ID =
			"SELECT * FROM qol_short_answer WHERE question_short_answer_id=?;";
	
	private static final String STATEMENT_RETRIEVE_SHORT_ANSWERS =
			"SELECT * FROM qol_short_answer;";
	
	private static final String STATEMENT_RETRIEVE_SHORT_ANSWERS_BY_LIMIT =
			"SELECT * FROM qol_short_answer LIMIT ?, ?;";
	
	private static final String STATEMENT_RETRIEVE_SHORT_ANSWERS_RANDOMLY =
			"SELECT * FROM qol_short_answer ORDER BY RAND(NOW()) LIMIT ?, ?;";
	
	private static final String STATEMENT_CREATE_SHORT_ANSWER =
			"INSERT INTO qol_short_answer " +
			"(fk_questionnaire_id, question_title) VALUES " +
			"(?, ?, ?);";
	
	private static final String STATEMENT_UPDATE_SHORT_ANSWER =
			"UPDATE qol_short_answer SET question_title=? " +
			"WHERE question_short_answer_id=?;";
	
	private static final String STATEMENT_DELETE_SHORT_ANSWER_BY_ID =
			"DELETE FROM qol_short_answer WHERE question_short_answer_id=?;";
	
	private static final String
			STATEMENT_DELETE_SHORT_ANSWERS_BY_QUESTIONNAIRE_ID =
				"DELETE FROM qol_short_answer WHERE fk_questionnaire_id=?;";
	
	private static ShortAnswerDataAccess _instance;
	
	private QolSchemaConnectionFactory _factory;
	
	private QuestionnaireDataAccess _questionnaireDataAccess;
	
	public static ShortAnswerDataAccess getInstance() {
		if (_instance == null) {
			_instance = new ShortAnswerDataAccess();
		}
		
		return _instance;
	}
	
	protected ShortAnswerDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
		_questionnaireDataAccess = QuestionnaireDataAccess.getInstance();
	}
	
	public ShortAnswer getShortAnswerById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_SHORT_ANSWER_BY_ID);
			ps.setLong(1, id);
			
			ResultSet rs = ps.executeQuery();
			if (!rs.next()) {
				return null;
			}
			return getShortAnswerByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public Collection<ShortAnswer> getShortAnswers() throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_SHORT_ANSWERS);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<ShortAnswer> shortAnswers = new LinkedList<ShortAnswer>();
			while (rs.next()) {
				ShortAnswer shortAnswer = getShortAnswerByResultSet(rs);
				shortAnswers.add(shortAnswer);
			}
			return shortAnswers;
		} finally {
			connection.close();
		}
	}
	
	public Collection<ShortAnswer> getShortAnswers(long offset, long row)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_SHORT_ANSWERS_BY_LIMIT);
			ps.setLong(1, offset);
			ps.setLong(2, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<ShortAnswer> shortAnswers = new LinkedList<ShortAnswer>();
			while (rs.next()) {
				ShortAnswer shortAnswer = getShortAnswerByResultSet(rs);
				shortAnswers.add(shortAnswer);
			}
			return shortAnswers;
		} finally {
			connection.close();
		}
	}
	
	public Collection<ShortAnswer> getShortAnswers(long row, boolean random)
			throws SQLException {
		if (!random) {
			return getShortAnswers(0, row);
		}
		
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_SHORT_ANSWERS_RANDOMLY);
			ps.setLong(1, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<ShortAnswer> shortAnswers = new LinkedList<ShortAnswer>();
			while (rs.next()) {
				ShortAnswer shortAnswer = getShortAnswerByResultSet(rs);
				shortAnswers.add(shortAnswer);
			}
			return shortAnswers;
		} finally {
			connection.close();
		}
	}
	
	public long createNewShortAnswer(ShortAnswer shortAnswer)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_SHORT_ANSWER, Statement.RETURN_GENERATED_KEYS);
			int questionnaireId =
					shortAnswer.getQuestionnaire().getQuestionnaireId();
			String questionTitle = shortAnswer.getQuestionTitle();
			ps.setInt(1, questionnaireId);
			ps.setString(2, questionTitle);
			ps.executeUpdate();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (!rs.next()) {
				return -1;
			}
			return rs.getLong(1);
		} finally {
			connection.close();
		}
	}
	
	public void updateShortAnswer(ShortAnswer shortAnswer)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_UPDATE_SHORT_ANSWER);
			long id = shortAnswer.getShortAnswerId();
			String questionTitle = shortAnswer.getQuestionTitle();
			ps.setString(1, questionTitle);
			ps.setLong(2, id);
			
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteShortAnswerById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_SHORT_ANSWER_BY_ID);
			ps.setLong(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteShortAnswersByQuestionnaireId(int questionnaireId)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_SHORT_ANSWERS_BY_QUESTIONNAIRE_ID);
			ps.setInt(1, questionnaireId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	private ShortAnswer getShortAnswerByResultSet(ResultSet rs)
			throws SQLException {
		long shortAnswerId = rs.getLong(COLUMN_SHORT_ANSWER_ID);
		int questionChoiceId = rs.getInt(COLUMNE_QUESTIONNAIRE_ID);
		String questionTitle = rs.getString(COLUMN_QUESTION_TITLE);
		
		Questionnaire questionnaire =
				_questionnaireDataAccess.getQuestionnaireById(questionChoiceId);
		
		ShortAnswer shortAnswer = new ShortAnswer();
		shortAnswer.setQuestionTitle(questionTitle);
		shortAnswer.setShortAnswerId(shortAnswerId);
		shortAnswer.setQuestionnaire(questionnaire);
		
		return shortAnswer;
	}

}
